// 数据库查询优化工具
import { performance } from 'perf_hooks';

class QueryOptimizer {
  constructor() {
    this.slowQueryThreshold = 1000; // 1秒
    this.queryCache = new Map();
    this.stats = {
      totalQueries: 0,
      slowQueries: 0,
      cachedQueries: 0,
      totalExecutionTime: 0
    };
  }

  // 查询执行监控
  async monitorQuery(queryName, queryFn, useCache = false, cacheKey = null, cacheTTL = 300) {
    const startTime = performance.now();
    
    // 检查缓存
    if (useCache && cacheKey) {
      const cached = this.getCachedQuery(cacheKey);
      if (cached) {
        this.stats.cachedQueries++;
        return cached;
      }
    }

    try {
      const result = await queryFn();
      const executionTime = performance.now() - startTime;
      
      // 更新统计
      this.stats.totalQueries++;
      this.stats.totalExecutionTime += executionTime;
      
      // 慢查询检测
      if (executionTime > this.slowQueryThreshold) {
        this.stats.slowQueries++;
        console.warn(`⚠️ 慢查询警告: ${queryName} 耗时 ${executionTime.toFixed(2)}ms`);
        
        // 记录慢查询详情
        this.logSlowQuery(queryName, executionTime, queryFn.toString());
      }
      
      // 缓存结果
      if (useCache && cacheKey) {
        this.cacheQuery(cacheKey, result, cacheTTL);
      }
      
      return result;
      
    } catch (error) {
      const executionTime = performance.now() - startTime;
      console.error(`❌ 查询执行失败: ${queryName}`, {
        error: error.message,
        executionTime: executionTime.toFixed(2) + 'ms'
      });
      throw error;
    }
  }

  // 查询缓存管理
  cacheQuery(key, result, ttl = 300) {
    const expiry = Date.now() + (ttl * 1000);
    this.queryCache.set(key, { result, expiry });
    
    // 清理过期缓存
    this.cleanExpiredCache();
  }

  getCachedQuery(key) {
    const cached = this.queryCache.get(key);
    if (cached && cached.expiry > Date.now()) {
      return cached.result;
    }
    
    // 移除过期缓存
    if (cached) {
      this.queryCache.delete(key);
    }
    
    return null;
  }

  cleanExpiredCache() {
    const now = Date.now();
    for (const [key, value] of this.queryCache.entries()) {
      if (value.expiry <= now) {
        this.queryCache.delete(key);
      }
    }
  }

  // 慢查询日志
  logSlowQuery(queryName, executionTime, queryString) {
    const slowQueryLog = {
      timestamp: new Date().toISOString(),
      queryName,
      executionTime: executionTime.toFixed(2) + 'ms',
      queryPreview: queryString.substring(0, 200) + '...'
    };
    
    // 这里可以集成到日志系统
    console.warn('慢查询详情:', slowQueryLog);
  }

  // 查询分析建议
  analyzeQueryPattern(query, params, executionPlan) {
    const suggestions = [];
    
    // 分析查询模式
    if (query.includes('SELECT *')) {
      suggestions.push({
        type: 'SELECT_OPTIMIZATION',
        message: '建议指定具体字段而不是使用SELECT *',
        priority: 'MEDIUM'
      });
    }
    
    if (query.includes('LIKE %') && !query.includes('INDEX')) {
      suggestions.push({
        type: 'INDEX_OPTIMIZATION',
        message: 'LIKE查询建议添加前缀索引',
        priority: 'HIGH'
      });
    }
    
    if (query.includes('ORDER BY RAND()')) {
      suggestions.push({
        type: 'RANDOM_OPTIMIZATION',
        message: 'ORDER BY RAND()性能较差，建议使用其他随机方法',
        priority: 'HIGH'
      });
    }
    
    // 分析执行计划
    if (executionPlan && executionPlan.plan) {
      const nodeType = executionPlan.plan['Node Type'];
      
      if (nodeType === 'Seq Scan') {
        suggestions.push({
          type: 'INDEX_MISSING',
          message: '检测到全表扫描，建议添加索引',
          priority: 'HIGH'
        });
      }
      
      if (executionPlan.plan['Total Cost'] > 1000) {
        suggestions.push({
          type: 'HIGH_COST_QUERY',
          message: '查询成本较高，建议优化',
          priority: 'MEDIUM'
        });
      }
    }
    
    return suggestions;
  }

  // 分页优化
  optimizePagination(query, page, pageSize, orderBy = 'id') {
    const offset = (page - 1) * pageSize;
    
    // 对于大数据集，使用游标分页
    if (page > 50) {
      return {
        method: 'CURSOR',
        suggestion: '建议使用基于游标的分页替代OFFSET分页',
        cursorField: orderBy,
        limit: pageSize
      };
    }
    
    return {
      method: 'OFFSET',
      offset,
      limit: pageSize
    };
  }

  // 批量操作优化
  async optimizeBatchOperations(operations, batchSize = 100) {
    const results = [];
    
    for (let i = 0; i < operations.length; i += batchSize) {
      const batch = operations.slice(i, i + batchSize);
      const batchResult = await Promise.allSettled(batch.map(op => op()));
      results.push(...batchResult);
      
      // 添加延迟避免数据库压力
      if (i + batchSize < operations.length) {
        await new Promise(resolve => setTimeout(resolve, 10));
      }
    }
    
    return results;
  }

  // 获取统计信息
  getStats() {
    const avgExecutionTime = this.stats.totalQueries > 0 
      ? (this.stats.totalExecutionTime / this.stats.totalQueries).toFixed(2)
      : 0;
    
    const slowQueryRate = this.stats.totalQueries > 0 
      ? ((this.stats.slowQueries / this.stats.totalQueries) * 100).toFixed(2)
      : 0;
    
    const cacheHitRate = (this.stats.totalQueries - this.stats.cachedQueries) > 0 
      ? ((this.stats.cachedQueries / this.stats.totalQueries) * 100).toFixed(2)
      : 0;
    
    return {
      ...this.stats,
      avgExecutionTime: avgExecutionTime + 'ms',
      slowQueryRate: slowQueryRate + '%',
      cacheHitRate: cacheHitRate + '%',
      cacheSize: this.queryCache.size
    };
  }

  // 重置统计
  resetStats() {
    this.stats = {
      totalQueries: 0,
      slowQueries: 0,
      cachedQueries: 0,
      totalExecutionTime: 0
    };
    this.queryCache.clear();
  }
}

// 查询构建器
class QueryBuilder {
  constructor() {
    this.query = {
      select: [],
      from: '',
      where: [],
      joins: [],
      orderBy: [],
      groupBy: [],
      limit: null,
      offset: null
    };
  }

  select(fields) {
    this.query.select = Array.isArray(fields) ? fields : [fields];
    return this;
  }

  from(table) {
    this.query.from = table;
    return this;
  }

  where(condition, params = {}) {
    this.query.where.push({ condition, params });
    return this;
  }

  join(table, on, type = 'INNER') {
    this.query.joins.push({ table, on, type });
    return this;
  }

  orderBy(field, direction = 'ASC') {
    this.query.orderBy.push({ field, direction });
    return this;
  }

  groupBy(fields) {
    this.query.groupBy = Array.isArray(fields) ? fields : [fields];
    return this;
  }

  limit(count) {
    this.query.limit = count;
    return this;
  }

  offset(count) {
    this.query.offset = count;
    return this;
  }

  build() {
    let sql = `SELECT ${this.query.select.join(', ')} FROM ${this.query.from}`;
    
    // 添加JOIN
    this.query.joins.forEach(join => {
      sql += ` ${join.type} JOIN ${join.table} ON ${join.on}`;
    });
    
    // 添加WHERE条件
    if (this.query.where.length > 0) {
      const whereConditions = this.query.where.map(w => w.condition).join(' AND ');
      sql += ` WHERE ${whereConditions}`;
    }
    
    // 添加GROUP BY
    if (this.query.groupBy.length > 0) {
      sql += ` GROUP BY ${this.query.groupBy.join(', ')}`;
    }
    
    // 添加ORDER BY
    if (this.query.orderBy.length > 0) {
      const orderClauses = this.query.orderBy.map(o => `${o.field} ${o.direction}`);
      sql += ` ORDER BY ${orderClauses.join(', ')}`;
    }
    
    // 添加LIMIT和OFFSET
    if (this.query.limit) {
      sql += ` LIMIT ${this.query.limit}`;
    }
    
    if (this.query.offset) {
      sql += ` OFFSET ${this.query.offset}`;
    }
    
    // 收集参数
    const params = {};
    this.query.where.forEach((w, index) => {
      Object.assign(params, w.params);
    });
    
    return { sql, params };
  }
}

// 导出优化工具
const queryOptimizer = new QueryOptimizer();

export {
  queryOptimizer,
  QueryBuilder,
  QueryOptimizer
};